﻿IF EXISTS (SELECT * FROM sys.procedures WHERE Name = 'SendErrorReportEmail')
BEGIN
	DROP PROCEDURE dbo.SendErrorReportEmail
END
GO

CREATE PROCEDURE dbo.SendErrorReportEmail
(
	@StartDate DATETIME,
	@EndDate DATETIME
)
AS BEGIN

	SET NOCOUNT ON

	DECLARE @UtcOffsetMinutes INT
	SET @UtcOffsetMinutes = DATEDIFF(MINUTE, GETUTCDATE(), GETDATE())

	DECLARE @Errors VARCHAR(MAX)
	SET @Errors = ''

	DECLARE @LogId AS VARCHAR(MAX)
	DECLARE @EntryDate AS VARCHAR(MAX)
	DECLARE @Severity AS VARCHAR(MAX)
	DECLARE @CategoryName AS VARCHAR(MAX)
	DECLARE @Title AS VARCHAR(MAX)
	DECLARE @Description AS VARCHAR(MAX)
	DECLARE @Username AS VARCHAR(MAX)
	DECLARE @ServerName AS VARCHAR(MAX)

	DECLARE cur CURSOR FAST_FORWARD READ_ONLY FOR
		SELECT
			ISNULL(CAST(LogID AS VARCHAR), '(null)'),
			ISNULL(CONVERT(VARCHAR, DATEADD(MINUTE, @UtcOffsetMinutes, [Timestamp]), 109), '(null)'),
			ISNULL(Severity, '(null)'),
			ISNULL(CategoryName, '(null)'),
			ISNULL(Title, '(null)'),
			ISNULL(REPLACE(Message, ' at ', '<br />at '), '(null)'),
			ISNULL(RTRIM(LTRIM(SUBSTRING(FormattedMessage, CHARINDEX('Extended Properties:', FormattedMessage) + LEN('Extended Properties:'), 1000))), '(null)'),
			ISNULL(MachineName, '(null)')
		FROM Log
			INNER JOIN CategoryLog ON (CategoryLog.LogID = Log.LogID)
			INNER JOIN Category ON (CategoryLog.CategoryID = Category.CategoryID)
		WHERE
			AND EntryDt BETWEEN @StartDate AND @EndDate
		ORDER BY EntryDt

	OPEN cur
	FETCH NEXT FROM cur INTO @LogId, @EntryDate, @Severity, @CategoryName, @Title, @Description, @Username, @ServerName

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @Errors = @Errors +

		'<table border="1">
			<tr>
				<th>LogID:</th>
				<td>' + @LogId + '</td>
			</tr>

			<tr>
				<th>EntryDt:</th>
				<td>' + @EntryDate + '</td>
			</tr>

			<tr>
				<th>Severity:</th>
				<td>' + @Severity + '</td>
			</tr>
			
			<tr>
				<th>Category:</th>
				<td>' + @CategoryName + '</td>
			</tr>
			
			<tr>
				<th>Title:</th>
				<td>' + @Title + '</td>
			</tr>

			<tr>
				<th>Description:</th>
				<td>' + @Description + '</td>
			</tr>

			<tr>
				<th>Username:</th>
				<td>' + @Username + '</td>
			</tr>

			<tr>
				<th>ServerName:</th>
				<td>' + @ServerName + '</td>
			</tr>

		</table>
		<br />'

		FETCH NEXT FROM cur INTO @LogId, @EntryDate, @Severity, @CategoryName, @Title, @Description, @Username, @ServerName
	END
	
	CLOSE cur
	DEALLOCATE cur

	IF LEN(@Errors) > 0
	BEGIN

		DECLARE @Category VARCHAR(15)
		SET @Category = 'ErrorReport'
		
		DECLARE @FromAddress VARCHAR(MAX)
		DECLARE @ToAddress VARCHAR(MAX)
		DECLARE @CCAddress VARCHAR(MAX)
		DECLARE @Subject VARCHAR(255)
		DECLARE @Body VARCHAR(MAX)

		SELECT
			@FromAddress = FromAddress,
			@ToAddress = ToAddress,
			@CCAddress = CCAddress,
			@Subject = Subject,
			@Body = Body
			
		FROM
			dbo.infEmailLookup

		WHERE
			Category = @Category
			AND
			ActiveInd = 'Y'

		IF (@FromAddress IS NOT NULL)
		AND (@ToAddress IS NOT NULL)
		AND (@Subject IS NOT NULL)
		AND (@Body IS NOT NULL)
		BEGIN

			SET @Subject = REPLACE(@Subject, '<!StartDate!>', CONVERT(VARCHAR, DATEADD(MINUTE, @UtcOffsetMinutes, @StartDate), 100))
			SET @Subject = REPLACE(@Subject, '<!EndDate!>', CONVERT(VARCHAR, DATEADD(MINUTE, @UtcOffsetMinutes, @EndDate), 100))
			
			SET @Body = REPLACE(@Body, '<!Title!>', @Subject)
			SET @Body = REPLACE(@Body, '<!Errors!>', @Errors)
			
			EXEC dbo.PSP_infEmailQ_Insert
				@EmailType = @Category,
				@DocumentLocation = NULL,
				@DocumentName = NULL,
				@Subject = @Subject,
				@EmailAddressFrom = @FromAddress,
				@EmailAddressTo = @ToAddress,
				@EmailAddressCC = @CCAddress,
				@EmailAddressBCC = NULL,
				@RequestedBy = NULL,
				@BodyText = @Body

		END

	END

END
GO

GRANT EXECUTE ON dbo.PSP_SendErrorReportEmail TO PackagingFormsUser
GO
 